#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2018-07-17 14:06:07
# @Author  : Yong XiaoDong (1647398574@qq.com)
# @Link    : http://www.hn-zc.com
# @Version : $Id$

import os
import pymysql
import sys
import random
import time

# 封装一个mysql类
class Mymysql:
	# 公共变量
	host = 'localhost'
	user = 'root'
	passwd = 'root'
	db = 'testdb'
	# 私有变量	
	__charset = 'utf8'
	
	# 初始化函数
	def __init__(self,h='',u='',p='',d=''):
		self.host = h if h!='' else self.host
		self.user = u if u!='' else self.user
		self.passwd = p if p!='' else self.passwd
		self.db = d if d!='' else self.db
		# print (self.host)
	# 连接数据库
	def mysql_connect(self):
		# 连接数据库
		global db_conn
		db_conn = pymysql.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db,charset=self.__charset)
		# 获取游标
		cursor = db_conn.cursor()
		return cursor
	# 查询表结构
	def select_table_desc(self,table):
		# 获取游标
		cursor = self.mysql_connect()
		# 查询表结构
		sql = "desc {0}".format(table)
		try:
			result = cursor.execute(sql)
			print ('表{0}的结构为:{1}'.format(table,result))
			final_res = result
		except Exception as e:
			print ('表{0}的结构查询失败'.format(table))
			final_res = False
		db_conn.close()
		return final_res
	# 根据字段查询一条数据(table:表名,field:字段,field_val:字段值)
	def get_field_one(self,table,field,field_val):
		# 获取游标
		cursor = self.mysql_connect()
		# 根据id查询数据的sql语句
		sql = "select * from {0} where {1}={2}".format(table,field,field_val)
		# 查询数据
		try:
			cursor.execute(sql)
			result = cursor.fetchone()
			content = '当前时间：{4} 根据{0}={1},查询表{2}查到的数据为:{3}'.format(field,field_val,table,result,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/get_field_one.txt',content=content)
			final_res = result
		except Exception as e:
			content = '当前时间：{4} 根据{0}={1},查询表{2}查到的数据为:{3}'.format(field,field_val,table,'null',self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/get_field_one_error.txt',content=content)
			final_res = False
		db_conn.close()
		return final_res
	# 随机查询一条数据(table:表名)
	def get_rand_one(self,table):
		# 获取游标
		cursor = self.mysql_connect()
		# 随机查询一条数据
		sql = "select * from {0} order by rand() limit 1".format(table)
		# 查询数据
		try:
			cursor.execute(sql)
			result = cursor.fetchone()
			content = '当前时间：{2} 随机查询表{0}查到的数据为:{1}'.format(table,result,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/get_rand_one.txt',content=content)
			final_res = result
		except Exception as e:
			content = '当前时间：{2} 随机查询表{0}查到的数据为:{1}'.format(table,'null',self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/get_rand_one_error.txt',content=content)
			final_res = False
		db_conn.close()
		return final_res
	# 查询此表所有数据(table:表名,field：字段,type:顺序还是倒序)
	def get_all_data(self,table,field,type):
		# 获取游标
		cursor = self.mysql_connect()
		# 查询此表所有数据
		sql = "select * from {0} order by {1} {2}".format(table,field,type)
		# 查询数据
		try:
			cursor.execute(sql)
			result = cursor.fetchall()
			content = '当前时间：{2} 查询表{0}所有数据:{1}'.format(table,result,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/get_all_data.txt',content=content)
			final_res = result
		except Exception as e:
			content = '当前时间：{2} 查询表{0}所有数据:{1}'.format(table,'null',self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/get_all_data_error.txt',content=content)
			final_res = False
		db_conn.close()
		return final_res
	# 增加一条数据(table:表名,dict:一条数据,字典)
	def add_one_data(self,table,dict):
		sql3 = self.dict_add_sql(dict)
		# 获取游标
		cursor = self.mysql_connect()
		# 插入一条数据sql
		sql = "insert into {0}{1}".format(table,sql3)
		# self.show_bug(sql)
		try:
			result = cursor.execute(sql)
			author_id = cursor.lastrowid
			content = '当前时间：{4} 插入表{0}的结果为：{1} 插入的数据为:{2},数据插入成功,返回id为:{3}'.format(table,result,dict,author_id,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/add_one_data.txt',content=content)
			final_res = result
		except Exception as e:
			content = '当前时间：{2} 插入表{0}失败 插入的数据为:{1}'.format(table,dict,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/add_one_data_error.txt',content=content)
			final_res = False
		db_conn.close()
		return final_res
	# 更新数据的值(table:表名,field:字段,field_val:字段值,dict:一条数据,字典)
	def update_data(self,table,field,field_val,dict):
		sql1 = self.dict_update_sql(dict)
		# 获取游标
		cursor = self.mysql_connect()
		# 插入一条数据sql
		sql = "update {0} set {1} where {2} = {3}".format(table,sql1,field,field_val)
		# self.show_bug(sql)
		try:
			result = cursor.execute(sql)
			if result==1:
				content = '当前时间：{5} 更新表{0}的结果为：{1} 更新的数据为:{2},更新的记录是字段{3}为:{4},数据更新成功'.format(table,result,dict,field,field_val,self.get_localtime())
				print (content)
				# 保存日志文件到本地
				self.write_file(filename='./logs/update_data.txt',content=content)
			else:
				content = '当前时间：{5} 更新表{0}的结果为：{1} 更新的数据为:{2},更新的记录是字段{3}为:{4},数据没有更新'.format(table,result,dict,field,field_val,self.get_localtime())
				print (content)
				# 保存日志文件到本地
				self.write_file(filename='./logs/update_data2.txt',content=content)
			final_res = result
		except Exception as e:
			content = '当前时间：{4} 更新表{0}失败 更新的数据为:{1},更新的记录是字段{2}为:{3}'.format(table,dict,field,field_val,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/update_data_error.txt',content=content)
			final_res = False
		db_conn.close()
		return final_res
	# 删除表数据(table:表名,field:字段,field_val:字段值)
	def delete_table_data(self,table,field,field_val):
		#获取游标
		cursor = self.mysql_connect()
		# 删除数据的sql语句
		sql = "delete from {0} where {1} = {2}".format(table,field,field_val)
		try:
			result = cursor.execute(sql)
			if result==1:
				content = '当前时间：{4} 删除表{0}里{1}为{2}的记录成功,执行结果为{3}'.format(table,field,field_val,result,self.get_localtime())
				print (content)
				# 保存日志文件到本地
				self.write_file(filename='./logs/delete_table_data.txt',content=content)
			else:
				content = '当前时间：{4} 删除表{0}里{1}为{2}的记录已经被删除,请勿多次执行,执行结果为{3}'.format(table,field,field_val,result,self.get_localtime())
				print (content)
				# 保存日志文件到本地
				self.write_file(filename='./logs/delete_table_data2.txt',content=content)
			final_res = result
		except Exception as e:
			content = '当前时间：{3} 删除表{0}里{1}为{2}的记录失败'.format(table,field,field_val,self.get_localtime())
			print (content)
			# 保存日志文件到本地
			self.write_file(filename='./logs/delete_table_data_error.txt',content=content)
			final_res = False
		db_conn.close()
		return final_res
	# 根据字典结构拼装跟新的sql语句
	def dict_update_sql(self,dict):
		sql = ''
		for key in dict:
			if dict[key] != '':
				if type(dict[key])==str:
					sql += "{0}='{1}',".format(key,dict[key])
				else:
					sql += "{0}={1},".format(key,dict[key])
		sql=sql[:-1]
		return sql
	# 根据字典结构拼装插入的sql语句
	def dict_add_sql(self,dict):
		sql = ''
		sql1 = ''
		sql2 = ''

		for key in dict:
			if dict[key] != '':
				if type(dict[key])==str:
					if dict[key] == 'null':
						sql1 += "{0},".format(key)
						sql2 += "{0},".format(dict[key])
					else:
						sql1 += "{0},".format(key)
						sql2 += "'{0}',".format(dict[key])
				else:
					sql1 += "{0},".format(key)
					sql2 += "{0},".format(dict[key])
		sql1=sql1[:-1]
		sql2=sql2[:-1]
		sql = '({0}) values ({1})'.format(sql1,sql2)
		return sql
	# 打印拦截函数
	def show_bug(self,msg):
		print (msg)
		sys.exit()
	# 保存日志文件到本地
	def write_file(self,filename,content):
		with open(filename,'a+',encoding='utf8') as file:
			file.write(content+"\n")
	# 获取北京时间并且格式化
	def get_localtime(self):
		time1 = time.localtime(time.time())
		time2 = time.strftime("%Y-%m-%d %H:%M:%S",time1)
		return time2
# 程序执行
# 说明：当前目录下必须有logs文件夹,不然日志记录存不了,代码也会执行错误
# 实例化Mymysql类(h:ip,u:用户名,p:密码,d:数据库)
Mymysql = Mymysql(h='',u='',p='',d='')

# 查询表结构(没什么用)
# res = Mymysql.select_table_desc(table='t_employee')

# 根据字段查询一条数据(table:表名,field:字段,field_val:字段值)
# res = Mymysql.get_field_one(table='t_employee',field='id',field_val='20')

# 随机查询一条数据(table:表名)
res = Mymysql.get_rand_one(table='t_employee')

# 查询此表所有数据(table:表名,field：字段,type:顺序还是倒序)
# res = Mymysql.get_all_data(table='t_employee',field='id',type='asc')

# 增加一条数据(table:表名,dict1:一条数据,字典) 说明：id代表一系列自动累增的主键
# dict1 = {'id':'null','first_name':'赛','last_name':'亚人','age':21,'sex':1,'income':random.randrange(1000)}
# res = Mymysql.add_one_data(table='t_employee',dict=dict1)

# 更新数据的值(table:表名,field:字段,dict:一条数据,字典)
# dict1 = {'first_name':'三','last_name':'郎神','age':21,'sex':0,'income':random.randrange(1000)}
# res = Mymysql.update_data(table='t_employee',field='id',field_val='18',dict=dict1)

# 删除表数据(table:表名,field:字段,field_val:字段值)
# res = Mymysql.delete_table_data(table='t_employee',field='id',field_val='19')
print (res)

